In an active data warehouse (ADW), data are fed into base tables in near real time. A workload on the ADW is usually a mix of both long running strategic queries and short tactic queries, which are run against the same set of tables. Join indexes, especially aggregate join indexes, can improve the performance of strategic queries significantly by precalculating the joins and aggregations. However, the frequent update to the base table and the existence of join indexes in such an environment may bring a big maintenance overhead.
There are two approaches to maintain join indexes in a database management system (DBMS). In direct maintenance, a join index table is updated in the same transaction that updates the base table. In deferred maintenance, the join index table is updated at a different time from the update to the base table. Each approach has its own pros and cons. Direct maintenance guarantees the data freshness in the join index but the overhead of maintaining the join index in real time may be a performance concern. Deferred maintenance overcomes the performance obstacle by delaying the join index update but queries may return inconsistent results due to the synchronization problem between the base table and join index.